package com.salou.cellar.contentprovider;

import static com.salou.cellar.dto.Bottle.COL_area_label;
import static com.salou.cellar.dto.Bottle.COL_boire_avant;
import static com.salou.cellar.dto.Bottle.COL_canal_vente;
import static com.salou.cellar.dto.Bottle.COL_comment;
import static com.salou.cellar.dto.Bottle.COL_coordX;
import static com.salou.cellar.dto.Bottle.COL_coordY;
import static com.salou.cellar.dto.Bottle.COL_coordZ;
import static com.salou.cellar.dto.Bottle.COL_cote;
import static com.salou.cellar.dto.Bottle.COL_country_label;
import static com.salou.cellar.dto.Bottle.COL_date_achat;
import static com.salou.cellar.dto.Bottle.COL_garde_max;
import static com.salou.cellar.dto.Bottle.COL_garde_min;
import static com.salou.cellar.dto.Bottle.COL_garde_optimum;
import static com.salou.cellar.dto.Bottle.COL_id;
import static com.salou.cellar.dto.Bottle.COL_label;
import static com.salou.cellar.dto.Bottle.COL_lieu_achat;
import static com.salou.cellar.dto.Bottle.COL_millesime;
import static com.salou.cellar.dto.Bottle.COL_nom;
import static com.salou.cellar.dto.Bottle.COL_nomCru;
import static com.salou.cellar.dto.Bottle.COL_prix;
import static com.salou.cellar.dto.Bottle.COL_quantite_achat;
import static com.salou.cellar.dto.Bottle.COL_quantite_courante;
import static com.salou.cellar.dto.Bottle.COL_subregion_label;
import static com.salou.cellar.dto.Bottle.COL_suggestion;
import static com.salou.cellar.dto.Bottle.COL_volume;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileReader;
import java.io.IOException;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteStatement;
import android.os.Bundle;
import android.os.Environment;
import android.os.Handler;
import android.os.Message;
import android.util.Log;

import com.salou.cellar.activity.helpers.Filter;
import com.salou.cellar.dto.Bottle;

/**
 * Provides content for the application + is able to import content from CSV. -
 * creates DB - is able to check the structure of a csv before inserting, by
 * comparing with structure currently handled
 * 
 * 
 * @author Loic
 */
public class CellarContentProvider extends SQLiteOpenHelper {

	private static final String CellarTable = "CellarTable";

	// "nomCru" "country_label" "subregion_label" "area_label" "label"
	// "millesime" "volume" "date_achat" "prix" "cote" "quantite_courante"
	// "quantite_achat" "garde_min" "garde_max" "garde_optimum" "suggestion"
	// "comment" "lieu_achat" "canal_vente" "nom" "coordX" "coordY" "coordZ"
	private String[] handledStructure = new String[] { COL_nomCru,
			COL_country_label, COL_subregion_label, COL_area_label, COL_label,
			COL_millesime, COL_volume, COL_date_achat, COL_prix, COL_cote,
			COL_quantite_courante, COL_quantite_achat, COL_garde_min,
			COL_garde_max, COL_garde_optimum, COL_suggestion, COL_comment,
			COL_lieu_achat, COL_canal_vente, COL_nom, COL_coordX, COL_coordY,
			COL_coordZ };

	private static final String CREATE_DB = "CREATE TABLE " + CellarTable
			+ " ( " + COL_id + " INTEGER PRIMARY KEY AUTOINCREMENT, "
			+ COL_nomCru + " TEXT NOT NULL, " + COL_boire_avant + " TEXT, "
			+ COL_quantite_courante + " TEXT NOT NULL, " + COL_millesime
			+ " TEXT, " + COL_country_label + " TEXT NOT NULL, "
			+ COL_subregion_label + " TEXT NOT NULL, " + COL_area_label
			+ " TEXT NOT NULL, " + COL_label + " TEXT NOT NULL, " + COL_volume
			+ " TEXT NOT NULL, " + COL_date_achat + " TEXT NOT NULL, "
			+ COL_prix + " TEXT, " + COL_cote + " TEXT, " + COL_quantite_achat
			+ " TEXT, " + COL_garde_min + " TEXT, " + COL_garde_max + " TEXT, "
			+ COL_garde_optimum + " TEXT, " + COL_suggestion + " TEXT, "
			+ COL_comment + " TEXT, " + COL_lieu_achat + " TEXT, "
			+ COL_canal_vente + " TEXT, " + COL_nom + " TEXT NOT NULL, "
			+ COL_coordX + " TEXT NOT NULL, " + COL_coordY + " TEXT NOT NULL, "
			+ COL_coordZ + " TEXT NOT NULL) ";

	private SQLiteDatabase cellarDB;
	private File esPublicDir;

	public CellarContentProvider(Context context) {
		super(context, "cellarDB", null, 29);
		cellarDB = getWritableDatabase();
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		internalCreateDB(db);
		Log.i("cellar db helper", "cellar db has been created");
	}

	@Override
	public void onOpen(SQLiteDatabase db) {
		super.onOpen(db);
		Log.i("cellar db helper", "cellar db is opening");
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// drop then create again the table in order to reset all
		internalDeleteDB(db);
		Log.i("cellar db helper", "cellar db is upgrading");
		onCreate(db);
	}

	private void importCSV(File csv, String separator, Handler listener)
			throws IOException {
		System.out.println("running in thread "
				+ Thread.currentThread().getName());
		// read first line and ensure structure is handled
		BufferedReader in = new BufferedReader(new FileReader(csv));
		String[] structure = in.readLine().split(separator);
		checkStructure(cellarDB, structure);
		int nbcols = structure.length;
		String line = null;
		int j = 0;
		int modulo = 10;
		while ((line = in.readLine()) != null) {
			String[] row = line.split(separator);
			ContentValues rowData = new ContentValues();
			for (int i = 0; i < nbcols; i++) {
				rowData.put(structure[i], row[i]);
			}
			long rc = cellarDB.insert(CellarTable, null, rowData);
			if (rc == -1l)
				break;
			if (listener != null && j++ % modulo == 0) {
				listener.sendEmptyMessage(modulo);
			}
		}
		listener.sendEmptyMessage(-1);
		System.out.println("DB loaded !");
	}

	// gets the structure and check if it matches the structure of the given
	// file
	// throws Exception in case of mismatch; return true if structures match or
	// false if structures could not be compared for any reason.
	private void checkStructure(SQLiteDatabase db, String[] fileStruct) {
		IllegalArgumentException e = new IllegalArgumentException(
				"File structure (" + fileStruct
						+ ") is not matching currently handled structure ("
						+ handledStructure + ")");
		if (!Arrays.equals(handledStructure, fileStruct))
			throw e;
	}

	public long getNbRows(Bundle currentFilterSet) {
		long count = 0;
		try {
			StringBuffer sb = new StringBuffer();
			if (!currentFilterSet.isEmpty())
				sb.append(" where ");
			Iterator<String> it = currentFilterSet.keySet().iterator();
			while (it.hasNext()) {
				String key = it.next();
				String value = (String) currentFilterSet.get(key);
				sb.append(key).append('=').append("'").append(value)
						.append("'");
				if (it.hasNext())
					sb.append(" and ");
			}
			SQLiteStatement s = cellarDB
					.compileStatement("select count(*) from " + CellarTable
							+ sb.toString());

			count = s.simpleQueryForLong();
		} catch (SQLException e) {
		}
		return count;
	}

	public Map<String, Long> getStats(Bundle filters, String groupBy) {
		if (filters.containsKey(groupBy))
			return null;
		String[] columns = filters.keySet().toArray(new String[0]);
		StringBuffer sb = new StringBuffer();
		for (int i = 0; i < columns.length; i++) {
			sb.append(columns[i]).append("=?");
			if (i < columns.length - 1)
				sb.append(" and ");
		}
		String[] values = new String[filters.size()];
		int j = 0;
		for (String key : filters.keySet())
			values[j++] = filters.getString(key);

		Cursor cs = cellarDB.query(CellarTable, new String[] { groupBy,
				"count(*) " }, sb.toString(), values, groupBy, null, null);

		Map<String, Long> ret = new HashMap<String, Long>();
		for (int i = 0; i < cs.getCount(); i++) {
			if (cs.moveToNext()) {
				ret.put(cs.getString(0), cs.getLong(1));
			}
		}

		return ret;
	}

	public Cursor getBottlesCursor(Bundle filters) {

		String[] columns = filters.keySet().toArray(new String[0]);
		StringBuffer sb = new StringBuffer();
		for (int i = 0; i < columns.length; i++) {
			sb.append(columns[i]).append("=?");
			if (i < columns.length - 1)
				sb.append(" and ");
		}
		String[] values = new String[filters.size()];
		int j = 0;
		for (String key : filters.keySet())
			values[j++] = filters.getString(key);
		// Cursor cs = db.query(CellarTable, values, sb.toString(),
		// values, null, null, null);
		String[] cols = new String[Bottle.listFields.length + 1];
		cols[0] = COL_id;
		System.arraycopy(Bottle.listFields, 0, cols, 1,
				Bottle.listFields.length);
		Cursor cs = cellarDB.query(CellarTable, cols, sb.toString(), values,
				null, null, getSortClause());

		return cs;
	}

	private String getSortClause() {
		return COL_country_label + " asc, " + COL_subregion_label + " asc, "
				+ COL_area_label + " asc, " + COL_nomCru + " asc";
	}

	public List<Bottle> getBottles(Bundle filters) {
		return createBottles(getBottlesCursor(filters), Bottle.listFields);
	}

	private List<Bottle> createBottles(Cursor cs, String[] cols) {
		List<Bottle> l = new ArrayList<Bottle>();
		for (int i = 0; i < cs.getCount(); i++) {
			if (cs.moveToNext()) {
				l.add(createBottle(cs, cols));
			}
		}

		return l;
	}

	private Bottle createBottle(Cursor cs, String[] cols) {
		Bottle bottle = new Bottle(cs.getInt(cs.getColumnIndex(COL_id)));
		for (String attr : cols) {
			try {
				setAttribute(bottle, attr, cs);
			} catch (Exception e) {
				Log.e(getClass().getName(), "column '" + attr
						+ "' could not be set in the bottle", e);
			}
		}
		return bottle;
	}

	private void setAttribute(Bottle bottle, String attr, Cursor cs)
			throws IllegalAccessException, InvocationTargetException,
			SecurityException, NoSuchMethodException {
		int ix = cs.getColumnIndex(attr);
		String value = cs.getString(ix);
		String attr2 = attr.substring(0, 1).toUpperCase() + attr.substring(1);
		String methodName = "set" + attr2;
		Method m = bottle.getClass().getMethod(methodName,
				new Class[] { String.class });
		m.invoke(bottle, value);
	}

	public void firstLoad(Handler listener) {
		File csv = getImportedFile();
		try {
			importCSV(csv, ";", listener);
		} catch (IOException e) {
			e.printStackTrace();
		}
	}

	private File getImportedFile() {
		this.esPublicDir = Environment
				.getExternalStoragePublicDirectory(Context.DOWNLOAD_SERVICE);
		this.esPublicDir.list();
		File csv = new File(this.esPublicDir, "cellar.csv");
		return csv;
	}

	private void internalDeleteDB(SQLiteDatabase db) {
		try {
			db.execSQL("DROP TABLE " + CellarTable + ";");
		} catch (Exception e) {
			// maybe it did not exist yet...
		}
	}

	private void internalCreateDB(SQLiteDatabase db) {
		db.execSQL(CREATE_DB);
	}

	public int getUploadableBottlesNumber() throws IOException {
		File csv = getImportedFile();
		FileReader fr = new FileReader(csv);
		BufferedReader in = new BufferedReader(fr);
		int nb = -1; // do not count the headers row
		while (in.readLine() != null)
			nb++;

		return nb;
	}

	public void deleteDB() {
		internalDeleteDB(cellarDB);
	}

	public void createDB() {
		internalCreateDB(cellarDB);
	}

	public Bottle getBottle(int id) {
		Cursor cs = cellarDB.query(CellarTable, Bottle.detailFields, "_id=?",
				new String[] { String.valueOf(id) }, null, null, null);
		Bottle b = null;
		try {
			cs.moveToFirst();
			b = createBottle(cs, Bottle.detailFields);
		} finally {
			cs.close();
		}
		return b;
	}

}
